# -*- coding: utf-8 -*-

from django.db import connections

from financial_daily.utils import DBUtils

class CNInfoDBAccessor:

    # 相关常数定义
    START_OFFSET = 0
    BATCH_COUNT = 500

    class Tables:
        STOCK_INFO = 'TB_PUBLIC_0007'
        INTITUTE_INFO = 'TB_PUBLIC_0001'

    class StockInfoColumns:
        CODE = 'OB_SECCODE_0007'
        SHORT_NAME = 'OB_SECNAME_0007'
        FULL_NAME = 'F015V_0007'
        PINYIN_NAME = 'F001V_0007'
        CATEGORY = 'F003V_0007'
        
    class IntituteInfoColumns:
        SHORT_NAME = 'F022V_0001'
        FULL_NAME = 'OB_ORGNAME_0001'
        CATEGORY = 'F003V_0001'

    @staticmethod
    def get_stock_basic_info():

        columns_list = [
            CNInfoDBAccessor.StockInfoColumns.FULL_NAME,
            CNInfoDBAccessor.StockInfoColumns.PINYIN_NAME,
            CNInfoDBAccessor.StockInfoColumns.SHORT_NAME,
            CNInfoDBAccessor.StockInfoColumns.CODE,
        ]
        columns_str = ",".join(columns_list)
        sql_str = """SELECT %s FROM %s WHERE %s = '%s' ORDER BY %s LIMIT %s, %s""" 

        return DBUtils.get_data_by_batch(
                connections['cninfo'].cursor(),
                sql_str, 
                (    columns_str, 
                     CNInfoDBAccessor.Tables.STOCK_INFO, 
                     CNInfoDBAccessor.StockInfoColumns.CATEGORY, 
                     u'A股', 
                     CNInfoDBAccessor.StockInfoColumns.CODE
                ),
                CNInfoDBAccessor.START_OFFSET,
                CNInfoDBAccessor.BATCH_COUNT
               )
            
    @staticmethod
    def get_institute_basic_info():

        columns_list = [
            CNInfoDBAccessor.IntituteInfoColumns.SHORT_NAME,
            CNInfoDBAccessor.IntituteInfoColumns.FULL_NAME,
        ]
        columns_str = ",".join(columns_list)
        sql_str = """SELECT %s FROM %s WHERE %s like '%%%%%s' AND %s <> '' LIMIT %s, %s"""

        return DBUtils.get_data_by_batch(
                connections['cninfo'].cursor(),
                sql_str, 
                (    columns_str, 
                     CNInfoDBAccessor.Tables.INTITUTE_INFO, 
                     CNInfoDBAccessor.IntituteInfoColumns.CATEGORY, 
                     u'券商', 
                     CNInfoDBAccessor.IntituteInfoColumns.SHORT_NAME
                ),
                CNInfoDBAccessor.START_OFFSET,
                CNInfoDBAccessor.BATCH_COUNT
               )

